import pandas as pd
import time
import datetime
import mysql.connector
from mysql.connector import Error



def query_institution_Information():
    """查询养老机构基本信息表。

        Args:
            host (str): 数据库主机地址
            port (int): 数据库端口号
            username (str): 用户名
            password (str): 密码

        Returns:
            results: 查询结果

        Raises:
            ConnectionError: 当连接失败时抛出
            TimeoutError: 当连接超时时抛出
    """

    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="qy777777",
            database="AiAssistant"
        )
        cursor = mydb.cursor(dictionary=True)

        # 查询特定字段
        sql = "SELECT * FROM institution_Information"
        cursor.execute(sql)
        records = cursor.fetchall()

        print("工作记录查询结果:")
        for record in records:
            print(f"地区: {record['districtName']}, 养老机构: {record['institutionName']}")

        # 创建id到数据的映射
        # results = {record['id']: record for record in records}
        results = records

        return results

    except Error as e:
        print(f"查询失败: {e}")
        return []
    finally:
        if mydb.is_connected():
            cursor.close()
            mydb.close()


def count_insPersonLoginInfo(institutionId, userName, loginPassWord):
    """使用参数化查询"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='AiAssistant',
            user='root',
            password='qy777777'
        )

        if connection.is_connected():
            cursor = connection.cursor()

            # 参数化查询
            query = "SELECT COUNT(*) FROM insPersonLoginInfo WHERE id = %s AND userName = %s  AND  loginPassWord = %s"
            params = (institutionId, userName, loginPassWord)

            cursor.execute(query, params)
            count = cursor.fetchone()[0]

            print(f"养老机构id为 {institutionId} 且用户名为 '{userName}' 且登录密码为 '{loginPassWord}' 的用户数: {count}")
            return count

    except Error as e:
        print(f"数据库错误: {e}")
        return 0
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()




def query_insPersonLoginInfo_tokenid(userName):
    """查询养老机构基本信息表。

        Args:
            host (str): 数据库主机地址
            port (int): 数据库端口号
            username (str): 用户名
            password (str): 密码

        Returns:
            results: 查询结果

        Raises:
            ConnectionError: 当连接失败时抛出
            TimeoutError: 当连接超时时抛出
    """

    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="qy777777",
            database="AiAssistant"
        )
        cursor = mydb.cursor(dictionary=True)

        # 查询特定字段
        sql = "SELECT tokenId FROM insPersonLoginInfo WHERE userName = %s"
        cursor.execute(sql, (userName,))
        records = cursor.fetchone()

        print("tokenId记录查询结果:")
        print(" tokenId=  " + records['tokenId'])
        results = records

        return results

    except Error as e:
        print(f"查询失败: {e}")
        return []
    finally:
        if mydb.is_connected():
            cursor.close()
            mydb.close()



def update_insPersonLoginInfo_tokenid(userName , tokenId):
    """使用参数化查询"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='AiAssistant',
            user='root',
            password='qy777777'
        )

        if connection.is_connected():
            cursor = connection.cursor()

            # 参数化查询
            query = "UPDATE insPersonLoginInfo  SET tokenId = %s WHERE  userName = %s "
            params = (tokenId, userName)
            cursor.execute(query, params)
            # 提交事务
            connection.commit()

            print(f"用户名为 '{userName}'  tokenId 已经修改为: {tokenId}")
            return 1

    except Error as e:
        print(f"数据库错误: {e}")
        return 0
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()






#
# def insertdata2work_record(record):
#
#     # 连接到MySQL数据库
#     conn = mysql.connector.connect(
#         host='localhost',  # 通常是 'localhost'
#         user='root',  # 你的数据库用户名
#         password='qy777777',  # 你的数据库密码
#         database='SmartBadge'  # 你的数据库名
#     )
#
#     cursor = conn.cursor()
#
#     # current_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
#
#     current_time = time.strftime('%H:%M:%S', time.localtime())
#
#     # 创建SQL插入语句
#     sql = "INSERT INTO work_record (time_period, work_content) VALUES (%s, %s)"
#     val = (current_time, record)
#
#     # 执行SQL语句
#     cursor.execute(sql, val)
#
#     # 提交到数据库执行
#     conn.commit()
#
#     print(cursor.rowcount, "record inserted.")
#
#     # 关闭Connection
#     conn.close()








